import pymysql
import hashlib
import os
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()

class DatabaseManager:
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.connect()
        if self.conn and self.cursor:
            self.create_tables()
        else:
            raise ConnectionError("无法建立数据库连接，请检查配置")
    
    def connect(self):
        """(通过临时连接)先创建数据库"""
        db_name = os.getenv('DB_NAME', 'campus_news_db')
        try:
            # 先连接到MySQL服务器(不指定具体数据库)
            temp_conn = pymysql.connect(
                host=os.getenv('DB_HOST', 'localhost'),
                user=os.getenv('DB_USER', 'root'),
                password=os.getenv('DB_PASSWORD', ''),
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            temp_cursor = temp_conn.cursor()
            
            # 创建数据库(如果不存在)
            temp_cursor.execute(f"CREATE DATABASE IF NOT EXISTS {db_name}")
            temp_cursor.close()
            temp_conn.close()
            
            # 3. 连接到指定的数据库
            self.conn = pymysql.connect(
                host=os.getenv('DB_HOST', 'localhost'),
                user=os.getenv('DB_USER', 'root'),
                password=os.getenv('DB_PASSWORD', ''),
                database=db_name,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            self.cursor = self.conn.cursor()
            return True
        except Exception as e:
            raise ConnectionError(f"数据库连接失败: {str(e)}")
    
    def create_tables(self):
        """创建必要的数据表"""
        if not self.conn or not self.cursor:
            return False
        try:
            # 创建用户表
            self.cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50) UNIQUE NOT NULL,
                    password VARCHAR(100) NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建新闻历史记录表
            self.cursor.execute('''
                CREATE TABLE IF NOT EXISTS news_history (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id INT NOT NULL,
                    record_id VARCHAR(50) NOT NULL,
                    timestamp DATETIME NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    title_keywords VARCHAR(255) NOT NULL,
                    news_type VARCHAR(50) NOT NULL,
                    txt_content TEXT NOT NULL,
                    doc_filename VARCHAR(255),
                    doc_content LONGBLOB,
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            ''')
            self.conn.commit()
            return True
        except Exception as e:
            print(f"创建表失败: {e}")
            self.conn.rollback()
            return False
    
    def hash_password(self, password):
        """对密码进行哈希处理"""
        return hashlib.sha256(password.encode()).hexdigest()
    
    def register_user(self, username, password):
        """注册新用户"""
        if not self.conn or not self.cursor:
            return False, "数据库不可用，请稍后再试"
            
        try:
            # 检查用户名是否已存在
            self.cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
            if self.cursor.fetchone():
                return False, "用户名已存在"
            
            # 插入新用户
            hashed_password = self.hash_password(password)
            self.cursor.execute(
                "INSERT INTO users (username, password) VALUES (%s, %s)",
                (username, hashed_password)
            )
            self.conn.commit()
            return True, "注册成功"
        except Exception as e:
            print(f"注册失败: {e}")
            self.conn.rollback()
            return False, f"注册失败: {str(e)}"
    
    def login_user(self, username, password):
        """用户登录验证"""
        if not self.conn or not self.cursor:
            return False, "数据库不可用，请稍后再试"
            
        try:
            # 查找用户
            self.cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
            user = self.cursor.fetchone()
            
            if not user:
                # 用户不存在
                return False, "用户名不存在"
            elif user['password'] == self.hash_password(password):
                # 用户存在且密码正确
                return True, user  # 返回用户信息
            else:
                # 用户存在但密码错误
                return False, "密码错误"
        except Exception as e:
            print(f"登录验证失败: {e}")
            return False, f"登录失败: {str(e)}"

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()